﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Reports.Rest
{
    public class DataLoader
    {
        public static void GetData(Reports.Rest.DataSet.DataDataTable data,string ConnectionString,string departName,DateTime DateF)
        {
            string QueryString = 
@"SELECT @DepartName as DepartName, vwGoodsDetailed.FullName AS GoodsName, 
CD.Parties.RestQntty AS Rest, CD.Parties.RetailPrice AS Price
FROM CD.Parties INNER JOIN
vwGoodsDetailed ON CD.Parties.GoodsId = vwGoodsDetailed.Id
WHERE CD.Parties.RestQntty > 0
ORDER BY vwGoodsDetailed.FullName ASC";
            string QueryString1 =
@"WITH DATA(PartyId,[SUM])
AS
(
SELECT  PartyId, SUM(Qntty)
FROM         dbo.vwGoodsFlow
WHERE     DocDate < @DocDate
GROUP BY PartyId
HAVING      (SUM(Qntty) > 0)
)
select @DepartName as DepartName, g.FullName as GoodsName,d.[sum] as Rest, p.RetailPrice as Price from DATA d INNER JOIN CD.Parties p
ON d.PartyId = p.Id INNER JOIN dbo.vwGoodsDetailed g
ON g.Id = p.GoodsId
order by GoodsName";
            string QueryString2 = @"WITH DATA(PartyId,[SUM])
AS
(
SELECT  CD.Parties.Id, SUM(CA.GoodsDocLines.Qntty)
FROM         CD.Parties INNER JOIN
CA.GoodsDocLines ON CD.Parties.Id = CA.GoodsDocLines.PartyId INNER JOIN
CA.Documents ON CA.GoodsDocLines.DocId = CA.Documents.Id
WHERE     CA.Documents.DocDate < @DocDate
GROUP BY CD.Parties.Id
HAVING      (SUM(Qntty) > 0)
)
select @DepartName as DepartName, g.FullName as GoodsName,d.[sum] as Rest, 
p.RetailPrice as Price,il.RetailMarkup , il.SupplierPrice 
from DATA d INNER JOIN CD.Parties p
ON d.PartyId = p.Id INNER JOIN dbo.vwGoodsDetailed g
ON g.Id = p.GoodsId left outer join dbo.vwInputLines il
on p.Id = il.PartyId
order by GoodsName";
            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    SqlCommand command = new SqlCommand(QueryString2, connection);
                    SqlParameter DepartName = new SqlParameter("@DepartName", SqlDbType.VarChar, 50);
                    if (string.IsNullOrEmpty(departName))
                        departName = "Аптека";
                    DepartName.Value = departName;
                    command.Parameters.Add(DepartName);
                    SqlParameter DocDate = new SqlParameter("@DocDate", SqlDbType.DateTime);
                    DocDate.Value = DateF;
                    command.Parameters.Add(DocDate);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(data);
                }
            }
            catch (SqlException se)
            {
                System.Windows.Forms.MessageBox.Show(se.Message);
            }
            catch (Exception ee)
            {
                System.Windows.Forms.MessageBox.Show(ee.Message);
            }
        }
    }
}
